package com.samson.empapp.dao; 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.CountriesDbo;
import com.samson.empapp.util.DbUtil;

public class CountriesDao implements ICountriesDao {
	
	static Logger logger = Logger.getLogger(CountriesDao.class);

	@Override
	public CountriesDbo findById(String countryId) {
		CountriesDbo dbo = null; 
		Connection con = DbUtil.getOracleXEConn(); 
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT  "); 
			sb.append("COUNTRY_ID countryId, COUNTRY_NAME countryName, REGION_ID regionId "); 
			sb.append("FROM COUNTRIES "); 
			sb.append("where COUNTRY_ID = upper('"+countryId+"') ");
			pstmt = con.prepareStatement(sb.toString());
			
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				dbo = new CountriesDbo();
				dbo.setCountryId(countryId);
				dbo.setCountryName(rs.getString("countryName"));
				dbo.setRegionId(rs.getInt("regionId"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return dbo;
	}

	@Override
	public List<CountriesDbo> findAll() {
		List<CountriesDbo> returnList = new ArrayList<CountriesDbo>();
		CountriesDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT "); 
			sb.append("COUNTRY_ID countryId, COUNTRY_NAME countryName, REGION_ID regionId "); 
			sb.append("FROM COUNTRIES "); 
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery(sb.toString());
			while (rs.next()) {
				obj = new CountriesDbo();
				obj.setCountryId(rs.getString("countryId"));
				obj.setCountryName(rs.getString("countryName"));				
				returnList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return returnList;
	}

	@Override
	public void insert(CountriesDbo dbo) throws Exception {
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"COUNTRIES\" "); 
			sb.append("(COUNTRY_ID ,COUNTRY_NAME,REGION_ID) VALUES "); 
			sb.append("(?, ?, ?) ");

			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			
			pstmt.setString(1, dbo.getCountryId());
			pstmt.setString(2, dbo.getCountryName());
			pstmt.setInt(3, dbo.getRegionId());
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;
		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb);
		}
		
	}

}
